package com.sh.data.engine.domain.shims.db2.manager;

import com.sh.data.engine.domain.shims.db.DbOptions;
import com.sh.data.engine.domain.shims.db.GenericDbManager;
import com.sh.data.engine.domain.shims.db.model.FieldInfoDomain;
import com.sh.data.engine.domain.shims.db.model.PreviewDataDomain;
import com.sh.data.engine.domain.shims.db.model.TableInfoDomain;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.ImmutablePair;
import org.apache.commons.lang3.tuple.Pair;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ObjectUtils;

import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author: mengzheng.mhc
 * @date: 2024/6/6 10:18
 */
public class Db2Manager extends GenericDbManager {

    private static final Logger log = LoggerFactory.getLogger(Db2Manager.class);
    private String schemaCharset = null;

    public Db2Manager(DbOptions options) {
        super(options);
        if (options != null) {
            StringBuilder result = new StringBuilder();
            Pattern pXM = Pattern.compile("schemaCharset=([^;]*)");
            Matcher mXM = pXM.matcher(options.getUrl());
            while (mXM.find()) {
                result.append(mXM.group(1));
            }
            schemaCharset = result.toString();
        }
    }

    static {
        String driverClass = "com.ibm.db2.jcc.DB2Driver";
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Could not load db driver class: " + driverClass);
        }
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName) throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where TYPE = 'T' AND creator IN (select SCHEMANAME from syscat.schemata WHERE DEFINER = ?)";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt =
                 createPreparedStatement(connection, sql, dbOptions.getUsername());
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where TYPE = 'T' AND creator = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }


    private static TableInfoDomain getTableDomain(String dbName, ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        Set<String> set = new HashSet<>();
        for (int index = 1; index <= meta.getColumnCount(); index++) {
            set.add(meta.getColumnLabel(index));
        }
        TableInfoDomain entity = new TableInfoDomain();
        entity.setDbName(dbName);
        entity.setSchema(rs.getString("creator"));
        entity.setTableName(rs.getString("name"));
        String remarks = rs.getString("remarks");
        entity.setTableComment(remarks);
        entity.setLastDdlTime(rs.getDate("alter_time").getTime());
        // 视图的fid都是0，不同schema的表id有可能相同
        entity.setTblId(rs.getLong("fid") == 0 ? null : rs.getLong("fid"));

        final String objectType = rs.getString("TYPE");

        TableInfoDomain.TblType tblType = null;
        switch (objectType) {
            case "T":
                tblType = TableInfoDomain.TblType.MANAGED_TABLE;
                break;
            case "V":
                tblType = TableInfoDomain.TblType.VIEW;
                break;
            default:
                break;
        }
        entity.setTblType(tblType);

        return entity;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName) throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where TYPE = 'V' AND creator IN (select SCHEMANAME from syscat.schemata WHERE DEFINER = ?)";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt =
                 createPreparedStatement(connection, sql, dbOptions.getUsername());
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getViewList(String dbName, String schema) throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where TYPE = 'V' AND creator = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName) throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where creator IN (select SCHEMANAME from syscat.schemata WHERE DEFINER = ?)";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt =
                 createPreparedStatement(connection, sql, dbOptions.getUsername());
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public List<TableInfoDomain> getTableAndViewList(String dbName, String schema)
        throws SQLException {

        if (StringUtils.isBlank(schema)) {
            return getTableAndViewList(dbName);
        }
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where creator = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list;
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String tableName)
        throws SQLException {

        if (StringUtils.isNotBlank(dbOptions.getSchema())) {
            return getTableInfoByTableName(dbName, dbOptions.getSchema(), tableName);
        }

        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where creator IN (select SCHEMANAME from syscat.schemata WHERE DEFINER = ?) AND name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt =
                 createPreparedStatement(connection, sql, dbOptions.getUsername(), tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public TableInfoDomain getTableInfoByTableName(String dbName, String schema, String tableName)
        throws SQLException {
        String sql =
            "SELECT name,creator,fid,TYPE,remarks,alter_time "
                + "from sysibm.systables "
                + "where creator = ? AND name = ?";

        List<TableInfoDomain> list = new LinkedList<>();

        try (Connection connection = getConnection();
             PreparedStatement pstmt = createPreparedStatement(connection, sql, schema, tableName);
             ResultSet rs = pstmt.executeQuery()) {
            while (rs != null && rs.next()) {
                final TableInfoDomain tableDomain = getTableDomain(dbName, rs);
                list.add(tableDomain);
            }
        }
        return list.isEmpty() ? null : list.get(0);
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String tableName) throws SQLException {

        if (StringUtils.isNotBlank(dbOptions.getSchema())) {
            return getFieldList(dbName, dbOptions.getSchema(), tableName);
        }

        return getFieldList(dbName, null, tableName);
    }

    private static String convertCode(String messString) {
        if (StringUtils.isBlank(messString)) {
            return messString;
        }

        String encode = "UTF-8";
        for (String encoding : Arrays.asList("GB2312", "ISO-8859-1", "GBK")) {
            try {
                if (messString.equals(new String(messString.getBytes(encoding), encoding))) {
                    encode = encoding;
                    break;
                }
            } catch (Exception e) {
                continue;
            }
        }

        try {
            return new String(messString.getBytes(encode), StandardCharsets.UTF_8);
        } catch (Exception e) {
            log.error("字符编码解析报错", e);
        }

        return messString.replaceAll("[^(a-zA-Z0-9\\u4e00-\\u9fa5)]", "");
    }

    @Override
    protected String getPreviewDataSql(String tableName) {
        String sql = "SELECT * FROM " + tableName + " FETCH FIRST 10 ROWS ONLY";
        return sql;
    }

    @Override
    public List<FieldInfoDomain> getFieldList(String dbName, String schema, String tableName)
        throws SQLException {
        List<Pair<Integer, FieldInfoDomain>> list = new ArrayList<>();

        try (Connection connection = getConnection()) {
            if (StringUtils.isBlank(schema)) {
                schema = connection.getSchema().toUpperCase();
            }

            List<Object> params = new ArrayList<>();
            params.add(StringUtils.upperCase(tableName));

            String columnSql = "SELECT * from SYSIBM.SYSCOLUMNS where TBNAME = ?";

            if (StringUtils.isNotBlank(schema)) {
                columnSql += " AND TBCREATOR = ?";
                params.add(schema);
            }

            try (PreparedStatement pstmt =
                     createPreparedStatement(connection, columnSql, params.toArray());
                 ResultSet rs = pstmt.executeQuery()) {

                while (rs != null && rs.next()) {
                    String fieldType = StringUtils.trim(rs.getString("COLTYPE"));
                    String columnName = StringUtils.trim(rs.getString("NAME"));
                    int columnSize = rs.getInt("LENGTH");
                    int decimalDigits = rs.getInt("SCALE");
                    int nullType =
                        "Y".equalsIgnoreCase(StringUtils.trimToEmpty(rs.getString("NULLS")))
                            ? 1
                            : 0; // 字段是否可为NULL，0-NO  1-YES
                    String remarks = rs.getString("REMARKS");
                    remarks = convertCode(remarks);
                    int colNo = rs.getInt("COLNO");

                    if (fieldType.equalsIgnoreCase("decimal")) {
                        fieldType = fieldType + "(" + columnSize + "," + decimalDigits + ")";
                    }
                    if (fieldType.equalsIgnoreCase("varchar") || fieldType.equalsIgnoreCase("char")) {
                        fieldType = fieldType + "(" + columnSize + ")";
                    }

                    FieldInfoDomain fieldInfo =
                        FieldInfoDomain.builder()
                            .fieldName(columnName)
                            .fieldType(fieldType)
                            .columnSize(columnSize)
                            .decimalDigits(decimalDigits)
                            .isNullable(nullType == 1)
                            .comment(remarks)
                            .build();

                    Pair<Integer, FieldInfoDomain> pair = ImmutablePair.of(colNo, fieldInfo);

                    list.add(pair);
                }
            }

            if (CollectionUtils.isEmpty(list)) {
                return null;
            }

            String pkSql =
                "SELECT a.colname FROM sysibm.syskeycoluse a "
                    + "INNER JOIN syscat.tabconst b ON a.tbname = b.TABNAME AND a.TBCREATOR = b.TABSCHEMA AND b.TYPE='P' "
                    + "WHERE a.tbname = ?";
            if (StringUtils.isNotBlank(schema)) {
                pkSql += " AND a.TBCREATOR = ?";
            }

            try (PreparedStatement pstmt = createPreparedStatement(connection, pkSql, params.toArray());
                 ResultSet rs = pstmt.executeQuery()) {
                while (rs != null && rs.next()) {
                    String columnName = rs.getString("COLNAME");
                    Optional<Pair<Integer, FieldInfoDomain>> optional =
                        list.stream()
                            .filter(e -> StringUtils.equals(e.getValue().getFieldName(), columnName))
                            .findFirst();
                    if (optional.isPresent()) {
                        Pair<Integer, FieldInfoDomain> pair = optional.get();
                        if (!ObjectUtils.isEmpty(pair) && !ObjectUtils.isEmpty(pair.getValue())) {
                            pair.getValue().setPk(true);
                        }
                    }
                }
            }
        }

        list.sort(Comparator.comparing(Pair::getKey));

        return list.stream().map(e -> e.getValue()).collect(Collectors.toList());
    }

    @Override
    public boolean alterFieldDesc(
        String dbName, String tableName, String fieldName, String fieldDes, boolean isPartition)
        throws SQLException {
        throw new SQLException("暂未实现");
    }

    @Override
    public String getQueryVersionSql() {
        return "SELECT service_level FROM TABLE (sysproc.env_get_inst_info())";
    }

    @Override
    public PreviewDataDomain previewData(String schema, String tableName) throws SQLException {
        return super.previewData(escapeTableName(schema) + "." + escapeTableName(tableName));
    }

    @Override
    public String getQuerySchemaListSql() {
        return "select SCHEMANAME from syscat.schemata";
    }

    public static void main(String[] args) throws SQLException {
        DbOptions options = new DbOptions();
        options.setUrl("jdbc:db2://10.88.36.79:50000/testdb");
        options.setUsername("DB2INST1");
        options.setPassword("123456");

        Db2Manager manager = new Db2Manager(options);
        final String dbVersion = manager.getDbVersion();
        System.out.println(dbVersion);

        final List<TableInfoDomain> tableInfoDomainList = manager.getTableAndViewList("testdb");
        tableInfoDomainList.forEach(System.out::println);

        final List<FieldInfoDomain> fieldList = manager.getFieldList("testdb", "test2");
        fieldList.forEach(System.out::println);

        final PreviewDataDomain previewDataDomain = manager.previewData("test");
        System.out.println(previewDataDomain);
    }
}
